

/**
 * 
 * @param sheetName sheet 名字
 * @param columnRange 
 * @param func 
 * @param that 
 */
const protectRange = (sheetName: string, columnRange: string, func: Function, that?: any) => {
    Excel.run(function (ctx) {
        var sheet = ctx.workbook.worksheets.getItem(sheetName);
        // var allRange = sheet.getRange();
        // allRange.format.protection.locked = false;

        // var range = sheet.getUsedRange(true);
        var range = sheet.getRange(columnRange);
        if (range) {
            // 下面两个条件满足才能判断这单元格是否为空
            // range.values == '' && range.formulas == ''
            range.load(['address',
                'values', 'formulas', 'format/protection/locked']);
            ctx.sync()
                .then(function () {
                    console.log(`The range address is: "${range.address}".`);
                    console.log('Initial value of range.format.protection.locked: ' + range.format.protection.locked);
                    if (wlIsFunc(func)) {
                        // Excel.InsertShiftDirection.down
                        // func.call(that, range.values, range.formulas, range.format.protection.locked);
                        // func.call(that, 'xxxx')
                    }
                });
        };
        return ctx.sync();
    }).catch(errorHandler);
}

const addConfig = (func: any, ths: any) => {
    Excel.run(function (ctx:any) {
        var sheets = ctx.workbook.worksheets;
        let flag = false;
        sheets.load('items/name');
        ctx.sync().then(() => {
            sheets.items.every((sh: Excel.Worksheet) => {
                if (sh.name == 'config') {
                    func.call(ths);
                    flag = true;
                }
                return !flag;
            });
            if (flag) {
                func.call(ths, 'xxx');
            }else {
                sheets.add('config');
            }
            return ctx.sync();
        })
    }as any).catch(errorHandler);
};

/**
 * 获取页面已经使用的区域
 * @param sheetName 表名
 * @param func 回调函数
 * @param ths 回调函数 this
 */
const getUsedRange = (sheetName:string, func:Function, ths:any)=>{
    Excel.run(function(ctx:Excel.RequestContext){
        let sheet:Excel.Worksheet = ctx.workbook.worksheets.getItem(sheetName);
        let rng:Excel.Range = sheet.getUsedRange(true);
        rng.load('address, rowCount, columnCount');
        ctx.sync().then(()=>{
            if(wlIsFunc(func)){
                func.call(ths, rng.address, rng.rowCount, rng.columnCount);
            }
            return ctx.sync();
        });
    } as any);
}


/**
 * 
 * @param sheetName 表名
 * @param rangeName 单元格地址
 * @param val 单元格数据, 二维数组长度需要和单元格地址匹配
 * @param func 回调函数
 * @param ths 回调函数 this
 */
const setRangeValues = (sheetName:string, rangeName:string, val:any[][], func:Function, ths:any)=>{
    Excel.run(function(ctx:Excel.RequestContext){
        var sheet:Excel.Worksheet = ctx.workbook.worksheets.getItem(sheetName);
        var rng:Excel.Range = sheet.getRange(rangeName);
        rng.values = val;
        ctx.sync().then(()=>{
            if(wlIsFunc(func)){
                func.call(ths);
            }
            return ctx.sync();
        });
    } as any).catch(errorHandler);
};


export const officeUtils = {
    protectRange,
    addConfig,
    setRangeValues,
    getUsedRange,
};